import java.sql.*;

public class StudentMgmtService {
    // 登录验证（返回包含课程信息的User对象）
    public User login(String username, String password, String role) throws SQLException, ClassNotFoundException {
        String userSql = "SELECT * FROM users WHERE username=? AND password=? AND role=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement userStmt = conn.prepareStatement(userSql)) {
            userStmt.setString(1, username);
            userStmt.setString(2, password);
            userStmt.setString(3, role);
            ResultSet userRs = userStmt.executeQuery();
            if (!userRs.next()) {
                return null;
            }

            // 获取教师课程信息（仅教师角色）
            String course = "";
            if ("teacher".equals(role)) {
                String teacherSql = "SELECT course FROM teachers WHERE username=?";
                try (PreparedStatement teacherStmt = conn.prepareStatement(teacherSql)) {
                    teacherStmt.setString(1, username);
                    ResultSet teacherRs = teacherStmt.executeQuery();
                    if (teacherRs.next()) {
                        course = teacherRs.getString("course");
                    }
                }
            }

            return new User(username, role, password, course);
        }
    }

    // 学生：查看个人成绩
    public void studentViewScore(String username) throws SQLException, ClassNotFoundException {
        String sql = "SELECT java_score, os_score FROM students WHERE username=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                System.out.printf("Java成绩：%.1f，操作系统成绩：%.1f\n",
                        rs.getDouble("java_score"), rs.getDouble("os_score"));
            }
        }
    }

    // 教师：修改Java成绩（带课程权限验证）
    public void teacherUpdateJavaScore(String teacherCourse, String studentId, double javaScore) throws SQLException, ClassNotFoundException {
        if (!"Java".equals(teacherCourse)) {
            throw new SecurityException("无权限修改Java成绩，仅Java教师可操作");
        }
        String sql = "UPDATE students SET java_score=? WHERE student_id=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, javaScore);
            pstmt.setString(2, studentId);
            pstmt.executeUpdate();
            System.out.println("Java成绩修改成功");
        }
    }

    // 教师：修改OS成绩（带课程权限验证）
    public void teacherUpdateOSScore(String teacherCourse, String studentId, double osScore) throws SQLException, ClassNotFoundException {
        if (!"Operating System".equals(teacherCourse)) {
            throw new SecurityException("无权限修改OS成绩，仅教授操作系统的教师可操作");
        }
        String sql = "UPDATE students SET os_score=? WHERE student_id=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, osScore);
            pstmt.setString(2, studentId);
            pstmt.executeUpdate();
            System.out.println("OS成绩修改成功");
        }
    }

    // 教师：按姓名/学号查询学生
    public void queryStudentByKeyword(String keyword) throws SQLException, ClassNotFoundException {
        String sql = "SELECT s.student_id, s.java_score, s.os_score, u.username "+ 
                     "FROM students s JOIN users u ON s.username = u.username "+ 
                     "WHERE s.student_id = ? OR u.username = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, keyword);
            pstmt.setString(2, keyword);
            ResultSet rs = pstmt.executeQuery();
            boolean hasResult = false;
            while (rs.next()) {
                hasResult = true;
                System.out.printf("学号：%s，姓名：%s，Java成绩：%.1f，OS成绩：%.1f\n",
                        rs.getString("student_id"),
                        rs.getString("username"),
                        rs.getDouble("java_score"),
                        rs.getDouble("os_score"));
            }
            if (!hasResult) {
                System.out.println("未找到匹配的学生信息");
            }
        }
    }

    // 管理员：查看所有学生信息
    public void adminViewAllStudents() throws SQLException, ClassNotFoundException {
        String sql = "SELECT s.student_id, u.username, s.java_score, s.os_score " +
                     "FROM students s JOIN users u ON s.username = u.username";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            System.out.println("\n所有学生信息：");
            while (rs.next()) {
                System.out.printf("学号：%s，姓名：%s，Java成绩：%.1f，OS成绩：%.1f\n",
                        rs.getString("student_id"),
                        rs.getString("username"),
                        rs.getDouble("java_score"),
                        rs.getDouble("os_score"));
            }
        }
    }

    // 管理员：查看所有教师信息
    public void adminViewAllTeachers() throws SQLException, ClassNotFoundException {
        String sql = "SELECT t.username, t.course, u.password " +
                     "FROM teachers t JOIN users u ON t.username = u.username";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            System.out.println("\n所有教师信息：");
            while (rs.next()) {
                System.out.printf("姓名：%s，所授课程：%s，密码：%s\n",
                        rs.getString("username"),
                        rs.getString("course"),
                        rs.getString("password"));
            }
        }
    }

    // 管理员：按学号/姓名查询学生信息
    public void adminQueryStudentByKeyword(String keyword) throws SQLException, ClassNotFoundException {
        String sql = "SELECT s.student_id, u.username, s.java_score, s.os_score " +
                     "FROM students s JOIN users u ON s.username = u.username " +
                     "WHERE s.student_id = ? OR u.username = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, keyword);
            pstmt.setString(2, keyword);
            ResultSet rs = pstmt.executeQuery();
            boolean hasResult = false;
            System.out.println("\n查询结果：");
            while (rs.next()) {
                hasResult = true;
                System.out.printf("学号：%s，姓名：%s，Java成绩：%.1f，OS成绩：%.1f\n",
                        rs.getString("student_id"),
                        rs.getString("username"),
                        rs.getDouble("java_score"),
                        rs.getDouble("os_score"));
            }
            if (!hasResult) System.out.println("未找到匹配的学生信息");
        }
    }

    // 管理员：按姓名查询教师信息
    public void adminQueryTeacherByName(String teacherName) throws SQLException, ClassNotFoundException {
        String sql = "SELECT t.username, t.course, u.password " +
                     "FROM teachers t JOIN users u ON t.username = u.username " +
                     "WHERE u.username = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, teacherName);
            ResultSet rs = pstmt.executeQuery();
            boolean hasResult = false;
            System.out.println("\n查询结果：");
            while (rs.next()) {
                hasResult = true;
                System.out.printf("姓名：%s，所授课程：%s，密码：%s\n",
                        rs.getString("username"),
                        rs.getString("course"),
                        rs.getString("password"));
            }
            if (!hasResult) System.out.println("未找到匹配的教师信息");
        }
    }

    // 管理员：删除学生信息（按学号/姓名）
    public void adminDeleteStudent(String keyword) throws SQLException, ClassNotFoundException {
        // 先查询学生username（通过学号或姓名）
        String querySql = "SELECT s.username FROM students s JOIN users u ON s.username = u.username " +
                          "WHERE s.student_id = ? OR u.username = ?";
        // 删除students表记录
        String deleteStudentSql = "DELETE FROM students WHERE username = ?";
        // 删除users表记录（角色为student）
        String deleteUserSql = "DELETE FROM users WHERE username = ? AND role = 'student'";

        try (Connection conn = DatabaseUtil.getConnection()) {
            conn.setAutoCommit(false);
            // 查询要删除的学生username
            try (PreparedStatement queryStmt = conn.prepareStatement(querySql)) {
                queryStmt.setString(1, keyword);
                queryStmt.setString(2, keyword);
                ResultSet rs = queryStmt.executeQuery();
                if (!rs.next()) {
                    conn.rollback();
                    throw new SQLException("未找到匹配的学生信息");
                }
                String targetUsername = rs.getString("username");

                // 删除students表记录
                try (PreparedStatement deleteStudentStmt = conn.prepareStatement(deleteStudentSql)) {
                    deleteStudentStmt.setString(1, targetUsername);
                    int studentRows = deleteStudentStmt.executeUpdate();
                    if (studentRows == 0) {
                        conn.rollback();
                        throw new SQLException("学生信息删除失败（students表无记录）");
                    }

                    // 删除users表记录
                    try (PreparedStatement deleteUserStmt = conn.prepareStatement(deleteUserSql)) {
                        deleteUserStmt.setString(1, targetUsername);
                        int userRows = deleteUserStmt.executeUpdate();
                        if (userRows == 0) {
                            conn.rollback();
                            throw new SQLException("用户信息删除失败（非学生角色或无记录）");
                        }

                        conn.commit();
                        System.out.println("学生信息删除成功");
                    }
                }
            } catch (SQLException e) {
                conn.rollback();
                throw new SQLException("删除失败，已回滚：" + e.getMessage());
            }
        }
    }

    // 管理员：录入学生基本信息
    public void adminAddStudent(String username, String studentId, String password) throws SQLException, ClassNotFoundException {
        // 插入users表
        String sqlUser = "INSERT INTO users (username, role, password) VALUES (?, 'student', ?)";
        // 插入students表（成绩初始化为NULL）
        String sqlStudent = "INSERT INTO students (username, student_id) VALUES (?, ?)";

        try (Connection conn = DatabaseUtil.getConnection()) {
            conn.setAutoCommit(false);
            try (PreparedStatement pstmtUser = conn.prepareStatement(sqlUser);
                 PreparedStatement pstmtStudent = conn.prepareStatement(sqlStudent)) {
                pstmtUser.setString(1, username);
                pstmtUser.setString(2, password);
                pstmtUser.executeUpdate();

                pstmtStudent.setString(1, username);
                pstmtStudent.setString(2, studentId);
                pstmtStudent.executeUpdate();

                conn.commit();
                System.out.println("学生信息录入成功");
            } catch (SQLException e) {
                conn.rollback();
                throw new SQLException("录入失败，已回滚", e);
            }
        }
    }
}